insert overwrite table bidefault.dm_scatter_order_pick_sum_dt partition(dt)
SELECT
     dt                                                              AS input_date        -- 订单录入日期
     ,a.pick_network_code                                            as pick_network_code -- 取件网点
     ,max(a.pick_network_name)                                       as pick_network_name -- 取件网点
     ,pick_staff_code                                                as pick_staff_code   -- 取件业务员编码
     ,MAX(pick_staff_name)                                           as pick_staff_name   -- 取件业务员名字
     ,cancel_reason                                                  as cancel_reason     -- 取消原因
     ,order_source_code                                              as order_source_code -- 订单来源code
     ,order_source_name                                              as order_source_name -- 订单来源
     ,count(1)                                                       as order_num         -- 订单量
     ,SUM(if(order_status_code = 103,1,0))                           AS pick_num          -- 揽收成功总量
     ,SUM(if(order_status_code = 104,1,0))                           AS cancel_num        -- 取消量
     ,sum(case when a.pick_time<=a.pick_end_time then 1 else 0 end)  as timely_pick_num   -- 及时取件数
     ,sum(case when a.pick_time>a.pick_end_time then 1 else 0 end)   as overtime_pick_num -- 超时取件数
     ,dt
FROM jms_dwd.dwd_yl_oms_oms_order_incre_dt a
JOIN jms_dim.dim_tab_channel_mapping_online_base b on a.order_source_code = b.channel_code
WHERE a.dt >= date_add('{{ execution_date | cst_ds }}', -14)
      AND a.dt <= '{{ execution_date | cst_ds }}'
      AND a.is_delete = 1
      AND a.proxy_area_code <> 'Test001'
      AND a.pick_network_code <> '888888'
GROUP BY
    dt
    ,pick_network_code
    ,pick_staff_code
    ,cancel_reason
    ,order_source_code
    ,order_source_name
distribute by 1
;

